from sqlalchemy import create_engine, text
import pymysql
import pyodbc
"""
sqlalchemy的初步使用,用SQLAlchemy执行原生SQL：
"""
class SQLHelper(object):

    def __init__(self,s_host=None, s_port=None, s_user=None, s_password=None, s_database=None, s_charset=None,s_type=None):
        # 数据库的配置变量
        # SQLALCHEMY_DATABASE_URI = 'mssql+pyodbc://sa:123456@127.0.0.1:1433/echarts?driver=SQL Server'

        if s_type == "MYSQL":
            SQLALCHEMY = 'mysql+pymysql://' + s_user + ':' + s_password+ '@' + s_host + ':' + str(s_port) + '/' + s_database
        else:
            SQLALCHEMY = 'mssql+pyodbc://' + s_user+ ':' + s_password + '@' + s_host + ':' + str(s_port) + '/' + s_database+ '?driver=SQL Server'
            # SQLALCHEMY = 'mssql+pyodbc://'

        # 创建数据库引擎
        self.engine = create_engine(SQLALCHEMY,encoding=s_charset, echo=False)
    def fetchallJsonOne(self,sql,params=None):
        """
        获取单条
        :param sql:
        :param params:
        :return:返回字典
        """
        print('fetchone')
        with self.engine.connect() as con:
            if not params:
                params = []
            try:
                rest = con.execute(text(sql), params)
            except Exception as e:
                return None, repr(e)
            print(rest.cursor.description)
            field_li = [k[0].lower() for k in rest.cursor.description]
            try:
                data = rest.fetchone()
            except Exception as e:
                return None, repr(e)
            result = dict(zip(field_li, data))
            return result, None

    def fetchallListJson(self, sql, params=None):
        """
        获取所有
        :param sql:
        :param params:
        :return:返回列表字典
        """
        with self.engine.connect() as con:
            print('fetchallListJson')
            if not params:
                params = []
            print('进入')
            try:
                rest = con.execute(text(sql), params)
            except Exception as e:
                return None, repr(e)
            field_li = [k[0].lower() for k in rest.cursor.description]
            # 所有数据
            try:
                data_all = rest.fetchall()  # 查询所有数据，fetchall()或fetchone()要放到description后面，否则会报错
            except Exception as e:
                return None, repr(e)
            result_list = list()
            print('查询成功')
            for data in data_all:
                result = dict(zip(field_li, data))
                result_list.append(result)
            return result_list, None
    # 数据列表字典加字段
    def fetchallListJsonFiled(self, sql, params=None):

        with self.engine.connect() as con:
            if not params:
                params = []
            # 执行查询操作
            print('进入查询')
            try:
                rest = con.execute(text(sql), params)
            except Exception as e:
                return None, repr(e)
            field_li = [k[0].lower() for k in rest.cursor.description]
            filedList = [{'field': 'seqindex', 'title': '序号', 'width': 10, 'align': 'center', 'def': []}]
            c_with = int(90 / len(field_li))
            for index, file_i in enumerate(field_li):
                if index == len(field_li) - 1 and index != 0:
                    filedList.append(
                        {'field': file_i, 'title': file_i, 'width': 90 - int(90 / len(field_li)) * index, 'align': 'center',
                         'def': []})
                else:
                    filedList.append({'field': file_i, 'title': file_i, 'width': c_with, 'align': 'center', 'def': []})
            data_all = rest.fetchall()  # 查询所有数据，fetchall()或fetchone()要放到description后面，否则会报错

            result_list = list()
            print('查询成功')
            for data in data_all:
                result = dict(zip(field_li, data))
                result_list.append(result)
            objData = {}
            objData['filedList'] = filedList
            objData['data'] = result_list
            return objData,None

    def fetchallListSet(self,sql,params=None):
        """
                获取所有
                :param sql:
                :param params:
                :return:列表集合
                """
        with self.engine.connect() as con:
            print('fetchallListSet')
            if not params:
                params = []
            try:
                print(sql)
                rest = con.execute(text(sql), params)
            except Exception as e:
                return None, repr(e)
            filedlist =  [k[0].lower() for k in rest.cursor.description]
            print(filedlist)
            result = rest.fetchall()
            data = []
            if len(filedlist) == 0:
                data = []
            if len(filedlist) > 0:
                for i2 in range(len(filedlist)):
                    print(i2)
                    print(list(map(lambda x: x[i2], result)))
                    data.append(list(map(lambda x: x[i2], result)))
            print(data)
            return data,None

    def fetchallListFlied(self,sql,params=None):
        """
        获取所有
        :param sql:
        :param params:
        :return:列表字段
        """
        with self.engine.connect() as con:
            print('fetchallListFlied')
            if not params:
                params = []

            try:
                rest = con.execute(text(sql), params)
            except Exception as e:
                return None, repr(e)
            filedlist =  [k[0].lower() for k in rest.cursor.description]
            data = []
            result = rest.fetchall()
            if len(filedlist) == 0:
                data = []
            for itm in result:
                data.append(list(itm))
            objData = {}
            objData['data'] = data
            objData['filedlist'] = filedlist
            return objData,None

# sql = "select * from cart_users"
# ccc,err = SQLHelper().fetchallListJsonFiled(sql)
# print(ccc)
# print(err)
# ccc,err = SQLHelper().fetchallJsonOne(sql)
# print(ccc)
# print(err)
# ccc,err = SQLHelper().fetchallListJson(sql)
# print(ccc)
# print(err)
# ccc,err = SQLHelper().fetchallListSet(sql)
# print(ccc)
# print(err)
# ccc,err = SQLHelper().fetchallListFlied(sql)
# print(ccc)
# print(err)